package cn.btks.resultintegration.achievements.repository;

import cn.btks.resultintegration.achievements.pojo.AchievementsFileTemp;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @Author lijun
 * @Date 2021/2/20 9:15
 */
@Repository
public interface AchievementsFileTempRepository extends JpaRepository<AchievementsFileTemp,Long> {

    /**
     * 根据文件名查询所有文件
     * @param fileName
     * @return
     */
    List<AchievementsFileTemp> findAllByFileName(String fileName);

    /**
     * 根据成果类型查询所有文件
     * @param achievementsTypeId
     * @return
     */
    List<AchievementsFileTemp> findAllByAchievementsTypeId(Long achievementsTypeId);

    /**
     * 根据文件成果类型及师ID查询文件信息
     * @param achievementsTypeId
     * @param cityId
     * @return
     */
    List<AchievementsFileTemp> findAllByAchievementsTypeIdAndCityId(Long achievementsTypeId,Long cityId);

    /**
     * 根据文件成果类型及师ID查询文件路径(审核通过的且不为历史数据的)
     * @param achievementsTypeId
     * @param cityId
     * @return
     */
    @Query(value = "SELECT aft.file_path as filePath FROM achievements_file_temp aft LEFT JOIN achievements_temp act ON aft.achievements_id = act.id WHERE act.is_approved = 1 and aft.achievements_type_id = ?1 AND aft.city_id = ?2 AND aft.is_history = 0",nativeQuery = true)
    List<Map<String,Object>> findAllByAchievementsTypeIdAndCityId1(Long achievementsTypeId, Long cityId);

    /**
     * 根据文件成果类型及分类查询文件信息
     * @param achievementsTypeId
     * @param fileCategory
     * @return
     */
    List<AchievementsFileTemp> findAllByAchievementsTypeIdAndFileCategory(Long achievementsTypeId,Long fileCategory);

    /**
     * 根据文件成果类型及分类查询文件路径(审核通过的且不为历史数据的)
     * @param achievementsTypeId
     * @param fileCategory
     * @return
     */
    @Query(value = "SELECT aft.file_path as filePath FROM achievements_file_temp aft LEFT JOIN achievements_temp act ON aft.achievements_id = act.id WHERE act.is_approved = 1 and aft.achievements_type_id = ?1 AND aft.file_category = ?2 AND aft.is_history = 0",nativeQuery = true)
    List<Map<String,Object>> findAllByAchievementsTypeIdAndFileCategory1(Long achievementsTypeId,Long fileCategory);

    /**
     * 根据文件成果类型查询文件路径(审核通过的且不为历史数据的)
     * @param achievementsTypeId
     * @return
     */
    @Query(value = "SELECT aft.file_path as filePath FROM achievements_file_temp aft LEFT JOIN achievements_temp act ON aft.achievements_id = act.id WHERE act.is_approved = 1 and aft.achievements_type_id = ?1 AND aft.is_history = 0",nativeQuery = true)
    List<Map<String,Object>> findAllByAchievementsTypeIdAndFileCategory2(Long achievementsTypeId);

    /**
     * 根据成果ID及类型更新成果数据为历史数据
     * @param achievementsId
     * @param typeId
     * @param isHistory
     * @return
     */
    @Modifying
    @Query(value = "update achievements_file_temp set is_history = ?3 where achievements_id = ?1 and achievements_type_id = ?2",nativeQuery = true)
    int updateFileIsHistory(Long achievementsId,Long typeId,Integer isHistory);

    /**
     * 根据成果ID更新成果数据为历史数据
     * @param achievementsId
     * @param isHistory
     * @return
     */
    @Modifying
    @Query(value = "update achievements_file_temp set is_history = ?2 where achievements_id = ?1",nativeQuery = true)
    int updateFileIsHistory(Long achievementsId,Integer isHistory);

    @Modifying
    @Query(value = "delete from achievements_file_temp where achievements_id = ?1 and achievements_type_id = ?2",nativeQuery = true)
    int deleteEvidenceByAchievementsTempId(Long achievementsTempId,Long fileType);
}
